﻿using System;
using System.Data;
using System.Text;
using System.Data.SqlClient;
using Gift.DBUtility;//请先添加引用
using Gift.Model; 
namespace Gift.DAL
{
    /// <summary>
    /// 数据访问类MemberAccount。
    /// </summary>
    public class MemberAccount
    {
        public MemberAccount()
        { }
        #region  成员方法
        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(int Id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from tbMemberAccount");
            strSql.Append(" where Id=@Id ");
            SqlParameter[] parameters = {
					new SqlParameter("@Id", SqlDbType.Int,4)};
            parameters[0].Value = Id;

            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }


        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int Add(tbMemberAccount model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into tbMemberAccount(");
            strSql.Append("memberid,CardId,AddDate,UserdDate)");
            strSql.Append(" values (");
            strSql.Append("@memberid,@CardId,@AddDate,@UserdDate)");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
					new SqlParameter("@memberid", SqlDbType.Int,4),
					new SqlParameter("@CardId", SqlDbType.Int,4),
					new SqlParameter("@AddDate", SqlDbType.DateTime),
					new SqlParameter("@UserdDate", SqlDbType.DateTime)};
            parameters[0].Value = model.memberid;
            parameters[1].Value = model.CardId;
            parameters[2].Value = model.AddDate;
            parameters[3].Value = model.UserdDate;

            object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
            if (obj == null)
            {
                return 1;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }
        /// <summary>
        /// 更新一条数据
        /// </summary>
        public void Update(tbMemberAccount model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update tbMemberAccount set ");
            strSql.Append("memberid=@memberid,");
            strSql.Append("CardId=@CardId,");
            strSql.Append("AddDate=@AddDate,");
            strSql.Append("UserdDate=@UserdDate");
            strSql.Append(" where Id=@Id ");
            SqlParameter[] parameters = {
					new SqlParameter("@Id", SqlDbType.Int,4),
					new SqlParameter("@memberid", SqlDbType.Int,4),
					new SqlParameter("@CardId", SqlDbType.Int,4),
					new SqlParameter("@AddDate", SqlDbType.DateTime),
					new SqlParameter("@UserdDate", SqlDbType.DateTime)};
            parameters[0].Value = model.Id;
            parameters[1].Value = model.memberid;
            parameters[2].Value = model.CardId;
            parameters[4].Value = model.AddDate;
            parameters[5].Value = model.UserdDate;

            DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public void Delete(int Id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete tbMemberAccount ");
            strSql.Append(" where Id=@Id ");
            SqlParameter[] parameters = {
					new SqlParameter("@Id", SqlDbType.Int,4)};
            parameters[0].Value = Id;

            DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public tbMemberAccount GetModel(int Id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select Id,memberid,CardId,AddDate,UserdDate from tbMemberAccount ");
            strSql.Append(" where Id=@Id ");
            SqlParameter[] parameters = {
					new SqlParameter("@Id", SqlDbType.Int,4)};
            parameters[0].Value = Id;

            tbMemberAccount model = new tbMemberAccount();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0]["Id"].ToString() != "")
                {
                    model.Id = int.Parse(ds.Tables[0].Rows[0]["Id"].ToString());
                }
                if (ds.Tables[0].Rows[0]["memberid"].ToString() != "")
                {
                    model.memberid = int.Parse(ds.Tables[0].Rows[0]["memberid"].ToString());
                }
                if (ds.Tables[0].Rows[0]["CardId"].ToString() != "")
                {
                    model.CardId = int.Parse(ds.Tables[0].Rows[0]["CardId"].ToString());
                }
                
                if (ds.Tables[0].Rows[0]["AddDate"].ToString() != "")
                {
                    model.AddDate = DateTime.Parse(ds.Tables[0].Rows[0]["AddDate"].ToString());
                }
                if (ds.Tables[0].Rows[0]["UserdDate"].ToString() != "")
                {
                    model.UserdDate = DateTime.Parse(ds.Tables[0].Rows[0]["UserdDate"].ToString());
                }
                return model;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select Id,memberid,CardId,AddDate,UserdDate ");
            strSql.Append(" FROM tbMemberAccount ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetListByMemberId(int iMemberId)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select c.cardid,c.cardcode,c.cardprice,isnull(cm.UsedMoney,0) as UsedMoney, (c.cardprice - isnull(cm.UsedMoney,0)) as remainMoney, convert(char(10),c.endtime,121) as endtime,"
                           + " convert(char(10),c.lasttime,121) as lasttime, convert(char(10),cm.AddDate,121) as AddDate,convert(char(10),cm.UserdDate,121) as UserdDate from tbCard c join tbMemberAccount cm on c.cardid = cm.CardId ");
            strSql.Append(" where cm.memberId= @MemberId ");

            SqlParameter[] parameters = {
					new SqlParameter("@MemberId", SqlDbType.Int)};
            parameters[0].Value = iMemberId;

            return DbHelperSQL.Query(strSql.ToString(), parameters);

        }


        /// <summary>
        /// 获得数据列表
        /// </summary>
        public decimal GetRemainSumMoney(int iMemberId)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select isnull(sum(C.CardPrice) - sum(isnull(MA.UsedMoney,0)),0) as sumPrice from tbMemberAccount MA join tbCard C ON MA.CardId = C.CardID ");
            strSql.Append(" where convert(datetime,convert(char(10),getdate(),121)) <= convert(datetime,convert(char(10),C.LastTime,121)) ");
            strSql.Append(" and MA.MemberId=@MemberId ");

            SqlParameter[] parameters = {
					new SqlParameter("@MemberId", SqlDbType.Int)};
            parameters[0].Value = iMemberId;

            SqlDataReader dr = DbHelperSQL.ExecuteReader(strSql.ToString(), parameters);
            decimal dRemainSumMoney = 0;
            if (dr.Read())
            {
                dRemainSumMoney = decimal.Parse(dr[0].ToString());
            }
            return dRemainSumMoney;

        }

        public int UserSpendMoney(int dMemberId, decimal dProSumPrice,string sCardCodes,string sProductPrice,string sAppendSQL )
        {
            string sSQL = string.Empty;

            sSQL = "declare @remainSumMoney decimal "
                   + "  set @remainSumMoney = 0 "
                   + " select @remainSumMoney = (sum(C.CardPrice) - sum(isnull(MA.UsedMoney,0))) from tbMemberAccount MA join tbCard C ON MA.CardId = C.CardID "
                    + " where convert(datetime,convert(char(10),getdate(),121)) <= convert(datetime,convert(char(10),C.LastTime,121)) "
                    + "  and MA.Memberid=@MemberId "
                    + " if( isnull((select sum(cardPrice) as cardPrice from tbCard  where cardcode in (" + sCardCodes + ")),0) != " + sProductPrice + ")"
                    + "  begin select -2 end " 
                   + " else if(@remainSumMoney < @ProSumPrice)"
                    + " begin "
                    + " select -1 "
                    + " end "
                    + " else "
                    + " begin "

                       + " declare @remainMoney decimal "
                            + " declare @id int "
                            + " set @remainMoney = 0 "
                            + " set @id = 0 "
                            + " while @ProSumPrice > 0 "
                            + " begin "
                                + " select top 1 @id = cm.id, "
                                            + " @remainMoney = (c.cardprice - isnull(cm.UsedMoney,0))  "
                                 + " from tbCard c join tbMemberAccount cm on c.cardid = cm.CardId "
                                + " where convert(datetime,convert(char(10),getdate(),121)) <= convert(datetime,convert(char(10),c.LastTime,121))"
                                     + " and isnull(cm.UsedMoney,0) < c.cardprice and cm.Memberid=@MemberId "
                                + " order by c.lastTime "
                                 + " if @id = 0 begin set @proSumPrice=-1 select -1 end  "
                                 + " else "
                                     + " begin "
                                     + " if(@ProSumPrice >= @remainMoney) "
                                     + " begin "
                                         + " update tbMemberAccount set usedMoney = isnull(usedMoney,0) + @remainMoney,userdDate=getdate() where id =@id "
                                         + " set @ProSumPrice = @ProSumPrice - @remainMoney "
                                     + " end"
                                     + " else "
                                     + " begin "
                                         + " update tbMemberAccount set usedMoney = isnull(usedMoney,0) + @ProSumPrice,userdDate=getdate() where id =@id"
                                         + " set @ProSumPrice = 0 "
                                     + " end "
                                 + " end "
                            + " end "
                         + " end;"
                         + sAppendSQL
                         +"; select 1 ;";
            SqlParameter[] sPara = { new SqlParameter("@ProSumPrice", SqlDbType.Decimal),
                                       new SqlParameter("@MemberId",SqlDbType.Int)
                                   };
            sPara[0].Value = dProSumPrice;
            sPara[1].Value = dMemberId;
            SqlDataReader dr = DbHelperSQL.ExecuteReader(sSQL, sPara);
            if (dr.Read())
            {
                int iResult = int.Parse(dr[0].ToString());
                dr.Close();
                return iResult;
            }
            dr.Close();
            return -1;
        }


        public int ConvertToAccount(int iMemberId, int iCardId)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("if exists(select 1 from tbCard where cardid = @CardId and states = 1 and getdate() <= lasttime) ");
            strSql.Append(" begin ");
            strSql.Append("    delete from tbMemberAccount where CardId = @CardId and MemberId = @MemberId ");
            strSql.Append("   update tbCard set states = 2 where cardid = @CardId ");
            strSql.Append("   insert into tbMemberAccount(MemberId,CardId,AddDate) ");
            strSql.Append("   values(@MemberId,@CardId,getdate()) ");
            strSql.Append("  select c.cardid, c.cardcode, isnull(c.cardprice,0) as cardprice,c.cardtypeId from tbMemberAccount O join tbCard c ON o.CardId = c.cardid where  o.MemberId = @MemberId ");
            strSql.Append(" update DB_Member Set money = isnull(money,0) + (select cardprice from tbCard where cardid=@CardId)  where memberid = @MemberId");
            strSql.Append("   end  ");
            SqlParameter[] parameters = {
					new SqlParameter("@MemberId", SqlDbType.Int,4),
					new SqlParameter("@CardId", SqlDbType.Int,4)};
            parameters[0].Value = iMemberId;
            parameters[1].Value = iCardId;

            return DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
        }


        /*
        /// <summary>
        /// 分页获取数据列表
        /// </summary>
        public DataSet GetList(int PageSize,int PageIndex,string strWhere)
        {
            SqlParameter[] parameters = {
                    new SqlParameter("@tblName", SqlDbType.VarChar, 255),
                    new SqlParameter("@fldName", SqlDbType.VarChar, 255),
                    new SqlParameter("@PageSize", SqlDbType.Int),
                    new SqlParameter("@PageIndex", SqlDbType.Int),
                    new SqlParameter("@IsReCount", SqlDbType.Bit),
                    new SqlParameter("@OrderType", SqlDbType.Bit),
                    new SqlParameter("@strWhere", SqlDbType.VarChar,1000),
                    };
            parameters[0].Value = "tbMemberAccount";
            parameters[1].Value = "ID";
            parameters[2].Value = PageSize;
            parameters[3].Value = PageIndex;
            parameters[4].Value = 0;
            parameters[5].Value = 0;
            parameters[6].Value = strWhere;	
            return DbHelperSQL.RunProcedure("UP_GetRecordByPage",parameters,"ds");
        }*/

        #endregion  成员方法
    }
}

